package hedron.db;

import hedron.Application;
import hedron.db.model.Contact;
import hedron.db.model.ContactComment;
import hedron.db.model.ContactType;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ContactsManager {
	final DBManager db;

	public ContactsManager(IDManager ID, DBManager db) {
		this.db = db;
	}

	public Contact getCompanyContacts() {
		Connection conn = db.getConnection();
		if (conn != null) {
			try {
				Contact contact = new Contact();
				StringBuilder sql = new StringBuilder();
				sql.append("select * from ").append(contact.getTableName());
				sql.append(" WHERE CUSTOMER_ID = 0 ");

				ResultSet rs = db.query(conn, sql.toString());
				if (rs != null && db.next(rs)) {
					Contact result = new Contact(rs);
					db.close(rs);
					return result;
				}
			} finally {
				db.closeConnection(conn);
			}
		}
		return null;
	}

	public List<Contact> getContacts(String filter, int limit, String sortColumn, String direction) {
		List<Contact> result = new ArrayList<Contact>();
		Connection conn = db.getConnection();
		if (conn != null) {
			try {
				Contact contact = new Contact();
				ContactType contacttype = new ContactType();
				StringBuilder sql = new StringBuilder();
				sql.append("select * from ");
				sql.append(contact.getTableName());
				sql.append(" c left outer join ");
				sql.append(contacttype.getTableName());
				sql.append(" t ON c.TYPE_ID = t.TYPE_ID ");
				sql.append(" WHERE C_DELETED = FALSE ");
				if (filter != null && filter.length() > 0) {
					sql.append(" AND ");
					sql.append(db.prepareSearchCondition("SEARCHTEXT", filter));
				}
				sql.append(" ORDER BY ");
				sql.append(sortColumn);
				sql.append(' ');
				sql.append(direction);
				ResultSet rs = db.query(conn, sql.toString());
				if (rs != null) {
					while (db.next(rs)) {
						result.add(new Contact(rs));
						if (limit > 0) {
							limit--;
							if (limit == 0) {
								break;
							}
						}
					}
					db.close(rs);
				}
			} finally {
				db.closeConnection(conn);
			}
		}
		return result;
	}

	private Map<String, Long> typesToID = new HashMap<String, Long>();

	public long getTypeId(String typeName) {
		return typesToID.get(typeName);
	}

	public String[] getContactTypesNames() {
		typesToID.clear();
		List<ContactType> types = new ArrayList<ContactType>();
		Connection conn = db.getConnection();
		if (conn != null) {
			try {
				ContactType contacttype = new ContactType();
				StringBuilder sql = new StringBuilder();
				sql.append("select * from ");
				sql.append(contacttype.getTableName());
				sql.append(" ORDER BY TYPE_NAME");
				ResultSet rs = db.query(conn, sql.toString());
				if (rs != null) {
					while (db.next(rs)) {
						types.add(new ContactType(rs));
					}
					db.close(rs);
				}
			} finally {
				db.closeConnection(conn);
			}
		}

		String[] result = new String[types.size()];
		for (int i = 0; i < result.length; i++) {
			result[i] = types.get(i).TYPE_NAME;
			typesToID.put(result[i], types.get(i).getID());
		}
		return result;
	}

	public ArrayList<ContactType> getContactTypes() {
		typesToID.clear();
		ArrayList<ContactType> types = new ArrayList<ContactType>();
		Connection conn = db.getConnection();
		if (conn != null) {
			try {
				ContactType contacttype = new ContactType();
				StringBuilder sql = new StringBuilder();
				sql.append("select * from ");
				sql.append(contacttype.getTableName());
				sql.append(" ORDER BY TYPE_NAME");
				ResultSet rs = db.query(conn, sql.toString());
				if (rs != null) {
					while (db.next(rs)) {
						types.add(new ContactType(rs));
					}
					db.close(rs);
				}
			} finally {
				db.closeConnection(conn);
			}
		}
		return types;
	}

	public ArrayList<ContactComment> getContactComments(Contact contact) {

		ArrayList<ContactComment> comments = new ArrayList<ContactComment>();
		Connection conn = db.getConnection();
		if (conn != null) {
			try {
				ContactComment comment = new ContactComment(Application.getCurrentUserID());
				StringBuilder sql = new StringBuilder();
				sql.append("select * from ");
				sql.append(comment.getTableName());
				sql.append(" c left outer join HUSER u ON c.USER_ID=u.USER_ID ORDER BY C_DATE DESC");
				ResultSet rs = db.query(conn, sql.toString());
				if (rs != null) {
					while (db.next(rs)) {
						comments.add(new ContactComment(rs));
					}
					db.close(rs);
				}
			} finally {
				db.closeConnection(conn);
			}
		}
		return comments;
	}
}
